﻿using System.Data.SqlClient;
using KPIS.GERP.Models;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.Class;
using System;
using System.Linq;
using System.Web.Mvc;
using KPIS.GERP.WEB.GIMS.Class;
using System.Collections.Generic;
using System.Data;


namespace KPIS.GERP.WEB.GIMS.Controllers
{
    public class GuaranteeContractController : Controller
    {
        //
        // GET: /GuaranteeContract/

        #region GuaranteeContract

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ListDropdown"> Dropdown get id value to load page</param>
        /// <returns></returns>
        public ActionResult Index(int? ListDropdown = null, int? BidderColId = null, string StatusBid = null,
            int? ColId = null)
        {
            var service = new BidService.BidServiceClient();
            var model = (dynamic) null;
            DateTime? dateT = DateTime.Now;
            ViewBag.ListDropdown = Drobdown();

            if (BidderColId == null)
            {
                if (ListDropdown == null)
                {
                    model = service.SelectBidderCollateralGuarantee(dateT, 31);
                    //return View((IList<BidderCollateral>)service.SelectBidderCollateralGuarantee(dateT, 31));
                }
                else if (ListDropdown == 0)
                {
                    model = service.SelectBidderCollateralGuarantee(dateT, 31);
                }
                else
                {
                    int Id = Convert.ToInt32(ListDropdown);
                    model = service.SelectListBidderCollateralGuarantee(dateT, 31, Id);
                }
            }
            else
            {
                if (StatusBid == "คืน")
                {
                    service.UpdateBidderCollateralGuarantee(BidderColId ?? 0, StatusBid);
                    if (ListDropdown == null)
                    {
                        model = service.SelectBidderCollateralGuarantee(dateT, 31);
                    }
                    else
                    {
                        int Id = Convert.ToInt32(ListDropdown);
                        model = service.SelectListBidderCollateralGuarantee(dateT, 31, Id);
                    }
                }
                else if (StatusBid == "จ่าย")
                {
                    InsertGuarantee(BidderColId, StatusBid, ColId);
                    model = service.SelectBidderCollateralGuarantee(dateT, 31);
                }
            }

            return View(model);
            //return View(service.SelectBidderCollateralGuarantee(new KPIS.GERP.Models.ICM.BidderCollateral { ReturnDate = dateT }));
        }

        #endregion

        public List<SelectListItem> Drobdown()
        {
            List<SelectListItem> items = new List<SelectListItem>();
            items.Add(new SelectListItem {Text = "-- ทั้งหมด --", Value = "0", Selected = true});
            items.Add(new SelectListItem {Text = "หลักประกันสัญญา", Value = "29"});
            items.Add(new SelectListItem {Text = "หลักประกันซอง", Value = "30"});
            items.Add(new SelectListItem {Text = "หลักประกันผลงาน", Value = "73"});
            return items;
        }

        public void InsertGuarantee(int? BidderColId = null, string StatusBid = null, int? ColId = null)
        {
            try
            {
                IList<BidderCollateral> iSer = new List<BidderCollateral>();
                var service = new BidService.BidServiceClient();
                DateTime? dateT = DateTime.Now;
                var servicePrItem = new PurchaseRequestItemService.PurchaseRequestItemServiceClient();
                var servicePprItem = new PrePurchaseRequestItemService.PrePurchaseRequestItemServiceClient();
                var serviceApproval = new SysApprovalService.SysApprovalServiceClient();
                var modelGuarantee =
                    service.SelectBidderCollateralGuaranteeName(dateT, 31, BidderColId ?? 0).FirstOrDefault();
                //service.UpdateBidderCollateralGuarantee(BidderColId ?? 0, StatusBid);
                var modelBidderCol =
                    service.SelectBidderCollateral(new BidderCollateral() {BidderColId = BidderColId ?? 0},
                        new[] {"Bidder"}).FirstOrDefault();
                var modelPrItem =
                    servicePrItem.Select(new PurchaseRequestItem() {PrId = modelBidderCol.Bidder.PrId ?? 0}, null)
                        .FirstOrDefault();
                var modelPprItem =
                    servicePprItem.Select(new PrePurchaseRequestItem() {PrItemId = modelPrItem.PrItemId},
                        new[] {"PrePurchaseRequest", "MasBudgetExpense"}).FirstOrDefault();

                FIM_MoneyRequests.FIM_MoneyRequestsSoapClient serviceMR =
                    new FIM_MoneyRequests.FIM_MoneyRequestsSoapClient();
                FIM_MoneyRequests.M_FIM_MoneyRequests infoMR = new FIM_MoneyRequests.M_FIM_MoneyRequests();
                infoMR.bidder_col_Id = BidderColId ?? 0;
                int EventId = 0;
                string NameGuarantee = null;

                if (ColId == 29)
                {
                    EventId = 32;
                }
                else if (ColId == 30)
                {
                    EventId = 31;
                }
                else if (ColId == 73)
                {
                    EventId = 34;
                }
                if (modelGuarantee.NamePo == null && modelGuarantee.BidsName == null)
                {
                    NameGuarantee = "";
                }
                else if (modelGuarantee.BidsName == null)
                {
                    NameGuarantee = modelGuarantee.NamePo;
                }
                else if (modelGuarantee.NamePo == null)
                {
                    NameGuarantee = modelGuarantee.BidsName;
                }



                infoMR.event_seq = EventId;
                infoMR.bgm_source_seq = modelPprItem.BgSourceId;
                infoMR.bgm_year = modelPprItem.PrePurchaseRequest.BgYear;
                infoMR.bgm_exp_grp_seq = modelPprItem.MasBudgetExpense.BgExpGrpId;
                infoMR.money_req_date = dateT;
                infoMR.event_seq = EventId;
                infoMR.use_advance = "0";
                infoMR.advance_bgm_source_seq = null;
                infoMR.ref_no = serviceMR.GenerateNextMoneyRequestRefNo();
                infoMR.money_req_name = NameGuarantee;
                infoMR.emp_seq = GimsMembership.CurrentUserId;
                infoMR.record_status = "W";
                infoMR.created_by = GimsMembership.CurrentUserId;
                infoMR.created_when = DateTime.Now;
                string strMoneyReqSeq = serviceMR.Insert(infoMR);

                FIM_MoneyRequestDtls.FIM_MoneyRequestDtlsSoapClient serviceMRD =
                    new FIM_MoneyRequestDtls.FIM_MoneyRequestDtlsSoapClient();
                FIM_MoneyRequestDtls.M_FIM_MoneyRequestDtls infoMRD = new FIM_MoneyRequestDtls.M_FIM_MoneyRequestDtls();
                infoMRD.money_req_seq = Convert.ToInt32(strMoneyReqSeq);
                infoMRD.amount = Convert.ToDouble(modelBidderCol.Amount ?? 0);
                infoMRD.record_status = "W";
                infoMRD.created_by = GimsMembership.CurrentUserId;
                infoMRD.created_when = DateTime.Now;
                serviceMRD.Insert(infoMRD);

                Models.SYS.ViewModel.CreateTaskVM modelApprove = new Models.SYS.ViewModel.CreateTaskVM();
                modelApprove.Amount = modelBidderCol.Amount ?? 1;
                modelApprove.ApplyToEmpId = GimsMembership.CurrentUserId;
                // ApproveProcessEnum is obsolete
                //public enum ApproveProcessEnum
                //{
                //    ลาป่วย = 1,
                //    ลาคลอดบุตร = 14,
                //    ลากิจส่วนตัว = 15,
                //    ลาพักผ่อน = 16,
                //    ลาอุปสมบทหรือลาไปประกอบพิธีฮัจย์ = 17,
                //    ลาเข้ารับราชการทหาร = 18,
                //    ลาไปศึกษา_ฝึกอบรม_ดูงาน_หรือปฏิบัติการ = 19,
                //    ลาไปปฏิบัติงานในองค์การระหว่างประเทศ = 20,
                //    ลาติดตามคู่สมรส = 21,
                //    ลาเข้ารับการตรวจเลือกหรือลาเข้ารับการเตรียมพล = 22,
                //    ลาไปปฎิบัติหน้าที่ = 23,
                //    คำสั่งบรรจุ = 101,
                //    คำสั่ง = 901,
                //    คำสั่งปรับเพิ่มเงินเดือน = 1000,
                //    คำสั่งให้ออกจากราชการ = 2000,
                //    คำสั่งเกษียณอายุราชการ = 3000,
                //    ขออนุมัติแจ้งความประสงค์ = 6,
                //    ขออนุมัติขอเบิก = 7,
                //    ขออนุมัติขอซื้อขอจ้างเกิน๑๐๐๐๐๐บาท = 8,
                //    ขออนุมัติขอซื้อขอจ้างไม่เกิน๑๐๐๐๐๐บาท = 9,
                //    ขออนุมัติใบสั่งเกิน๑๐๐๐๐๐บาท = 10,
                //    ขออนุมัติใบสั่งไม่เกิน๑๐๐๐๐๐บาท = 11,
                //    ขออนุมัติประกาศ = 12,
                //    ขออนุมัติงานสัญญา = 13,
                //    การตรวจรับ = 24
                //}
                modelApprove.AppPrcId = 7; // (int)ApproveProcessEnum.ขออนุมัติขอเบิก;
                modelApprove.CreateByEmpId = GimsMembership.CurrentUserId;
                modelApprove.RefCode = infoMR.ref_no;
                modelApprove.RefId = Convert.ToInt32(strMoneyReqSeq);
                modelApprove.Title = infoMR.money_req_name;
                modelApprove.ConfirmAs = "P";
                modelApprove.ApproveAs = "A";
                service.UpdateBidderCollateralGuarantee(BidderColId ?? 0, StatusBid);
                serviceApproval.CreateTask(modelApprove);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ReportGuarantee(string StartDate, string EndDate)
        {
            DateTime dStartDate = Convert.ToDateTime(StartDate);
            DateTime dEndDate = Convert.ToDateTime(EndDate);

            DateTime? dtStartDateC = dStartDate;
            DateTime? dtEndDateC = dEndDate;

            DataTable dtb5 = Collateral(dtStartDateC.Value, dtEndDateC.Value);
            Dictionary<string, DataTable> dicDtb = new Dictionary<string, DataTable>();
            Dictionary<string, string> dic2 = new Dictionary<string, string>();
            double dSumRemain = 0;

            foreach (DataRow dr in dtb5.Rows)
            {
                double result;
                Double.TryParse(dr["total"] + "", out result);
                dSumRemain += result;
            }

            dicDtb.Add("Sheet1!A4", dtb5);
            dic2.Add("Sheet1!A2", "ตั้งแต่วันที่ " + (dtStartDateC.HasValue ? dtStartDateC.Value.ToLongDateString() : "-") + " ถึงวันที่ " + (dtEndDateC.HasValue ? dtEndDateC.Value.ToLongDateString() : "-"));
            dic2.Add("Sheet1!j" + (4 + dtb5.Rows.Count), dSumRemain.ToString("#,##0.00"));

             MailMerge mailMerge = new MailMerge();
             mailMerge.MergeExcel(Response, Server.MapPath("~/Templates/Collateral.xlsx"),
                string.Format("Collateral-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dicDtb, dic2);
            //break;
            return;
            
            //MailMerge mailMerge = new MailMerge();
            //var service = new BidService.BidServiceClient();

            //Dictionary<string, string> Dics = new Dictionary<string, string>();
            //DateTime dStartDate = Convert.ToDateTime(StartDate);
            //DateTime dEndDate = Convert.ToDateTime(EndDate);

            //var models = service.CollateralReport(dStartDate, dEndDate);
            //DataTable dtb = GimsUtilities.ToDataTable(models);
            //Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();
            //dtbs.Add("A2", dtb);

            //mailMerge.MergeExcel(Response, Server.MapPath("~/Templates/รายงานทะเบียนคุมหลักประกัน.xlsx"), string.Format("รายงานทะเบียนคุมหลักประกัน-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff"))
            //    , dtbs);
        }

        public DataTable Collateral(DateTime sdate, DateTime edate)
        {
            string strSQL;
            DataTable dtb = null;
            string sd = string.Format("'{0}-{1}-{2}'", sdate.Year, sdate.Month, sdate.Day);
            string ed = string.Format("'{0}-{1}-{2}'", edate.Year, edate.Month, edate.Day);

            using (var db = new GERPContext())
            { 
                try
                {
                     
                    strSQL = @"
select po.Code, c.NameTh, bid.amount as po_amount, r.receive_date as BidderColDate
, r.ref_no as r_ref_no
, r.amount as r_amount, col.ReturnDueDate

, p.pay_date
, p.ref_no as p_ref_no

, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, look.LookUpName
--, coalesce(r.amount, 0) - coalesce(p.amount, 0) as total
--, p.amount as p_amount
from fim_receives r 
inner join IcmBidderCollateral col on r.icm_po_con_col_seq = col.BidderColId 
left join IcmMasLookUp look on col.ColStatusId = look.LookUpId
left join IcmMasContact c on r.contact_seq = c.ConId
left join IcmPurchaseOrder po on col.BidderId = po.BidderId and po.RecordStatus in ('A', 'J')
left join (select quo.BidderId, sum(quo.NetAmount) as amount
	from IcmBidderQuotation quo
	group by quo.BidderId
	) bid on po.BidderId = bid.BidderId
left join (select p.pay_seq, p.amount, p.ref_no
, case when p.method_seq in (1, 3)
	then p.real_pay_date
	else case when p.method_seq = 2
		then chq.pay_date
		else pip.pip_date
		end
	end as pay_date
from fim_payments p
left join fim_payment_cheques pc on p.pay_seq = pc.pay_seq
left join fim_cheques chq on pc.cheque_seq = chq.cheque_seq
left join fim_payments_payinpack pip on p.pay_seq = pip.pay_seq
where p.record_status = 'A'
) p on r.pay_seq = p.pay_seq and p.pay_date is not null
where r.record_status not like 'D'
and r.receive_date between "+sd+" and "+ed+@"

order by r.receive_date";
                    using (SqlConnection sqlConn = new SqlConnection(db.Database.Connection.ConnectionString))
                    using (SqlCommand cmd = new SqlCommand(strSQL, sqlConn))
                    {
                        sqlConn.Open();
                        DataTable dt = new DataTable();
                        dt.Load(cmd.ExecuteReader());
                        return dt;
                    }

                }
                catch (Exception ex)
                {
                    throw (ex);
                }
            }

            return dtb;
        }
        //public DataTable CopyGenericToDataTable<T>(IEnumerable<T> items)
        //{
        //    var properties = typeof(T).GetProperties();
        //    var result = new DataTable();

        //    //Build the columns
        //    foreach (var prop in properties)
        //    {
        //        result.Columns.Add(prop.Name, prop.PropertyType);
        //    }

        //    //Fill the DataTable
        //    foreach (var item in items)
        //    {
        //        var row = result.NewRow();

        //        foreach (var prop in properties)
        //        {
        //            var itemValue = prop.GetValue(item, new object[] { });
        //            row[prop.Name] = itemValue;
        //        }

        //        result.Rows.Add(row);
        //    }

        //    return result;
        //}

    }

    //public class Vm1
    //{
    //    public string code { get; set; }
    //    public string NameTh { get; set; }
    //    public DateTime? BidderColDate { get; set; }
    //    public DateTime? ReturnDate { get; set; }
    //    public Double? r_amount { get; set; }
    //    public Decimal? p_amount { get; set; }
    //    public string p_ref_no { get; set; }
    //    public Double? total { get; set; }
    //}
}